﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace CNative.DbUtils
{
    #region ISelectSqlBuilder
    public partial interface ISelectSqlBuilder : IQuerySql
    {
        /// <summary>
        /// 最多返回记录数
        /// </summary>
        /// <param name="top"></param>
        /// <returns></returns>
        ISelectSqlBuilder Top(int top = 1000);
        /// <summary>
        /// 开始查询
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="tableAlias"></param>
        /// <returns></returns>
        ISelectSqlBuilder From<TClass>(string tableAlias = "") where TClass : class, new();
        /// <summary>
        /// 开始查询(指定查询字段)
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="fields"></param>
        /// <param name="tableAlias"></param>
        /// <returns></returns>
        ISelectSqlBuilder From<TClass>(Expression<Func<TClass, dynamic>> fields, string tableAlias = "") where TClass : class, new();
        /// <summary>
        /// 添加查询字段
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="field"></param>
        /// <returns></returns>
        ISelectSqlBuilder Fields<TClass>(Expression<Func<TClass, dynamic>> fields = null, string tableAlias = "") where TClass : class, new();
        /// <summary>
        /// 添加查询字段
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="fields"></param>
        /// <returns></returns>
        ISelectSqlBuilder Fields(params string[] fields);

        /// <summary>
        /// 添加条件表达式
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="where"></param>
        /// <returns></returns>
        ISelectSqlBuilder Where<TClass>(Expression<Func<TClass, bool>> where, bool isAnd = true, string tableAlias = "") where TClass : class, new();
        /// <summary>
        /// 添加条件表达式
        /// </summary>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        ISelectSqlBuilder Where(string whereStr, params CNDbParameter[] dbParameters);


        /// <summary>
        /// 按列排序，OrderBy<TClass>(a => a.Time)
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="column"></param>
        /// <returns></returns>
        ISelectSqlBuilder OrderBy<TClass>(Expression<Func<TClass, dynamic>> column, string tableAlias = "") where TClass : class, new();
        /// <summary>
        /// 按列倒向排序，OrderByDescending<TClass>(a => a.Time)
        /// </summary>
        /// <param name="column">列</param>
        /// <returns></returns>
        ISelectSqlBuilder OrderByDescending<TClass>(Expression<Func<TClass, dynamic>> column, string tableAlias = "") where TClass : class, new();

        /// <summary>
        /// 按列分组查询 GroupBy<TClass>(a => a.Time)
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="column"></param>
        /// <returns></returns>
        ISelectSqlBuilder GroupBy<TClass>(Expression<Func<TClass, dynamic>> column, string tableAlias = "") where TClass : class, new();
        /// <summary>
        /// HAVING 子句可以筛选分组后的各组数据
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="expression">HAVING aggregate_function(column_name) operator value</param>
        /// <returns></returns>
        ISelectSqlBuilder Having<TClass>(Expression<Func<TClass, bool>> expression, string tableAlias = "") where TClass : class, new();

        /// <summary>
        /// 等值连接
        /// </summary>
        /// <typeparam name="TClassB">B表</typeparam>
        /// <param name="JoinWhere">关联条件</param>
        /// <returns>只返回两个表中联结字段相等的行</returns>
        ISelectSqlBuilder InnerJoin<TClassA, TClassB>(Expression<Func<TClassA, TClassB, bool>> JoinWhere, string tableAliasB = "") where TClassA : class, new() where TClassB : class, new();
        /// <summary>
        /// 左联接
        /// </summary>
        /// <typeparam name="TClassB">B表</typeparam>
        /// <param name="JoinWhere">关联条件</param>
        /// <returns>返回包括左表中的所有记录和右表中联结字段相等的记录</returns>
        ISelectSqlBuilder LeftJoin<TClassA, TClassB>(Expression<Func<TClassA, TClassB, bool>> JoinWhere, string tableAliasB = "") where TClassA : class, new() where TClassB : class, new();
        /// <summary>
        /// 右联接
        /// </summary>
        /// <typeparam name="TClassB">B表</typeparam>
        /// <param name="JoinWhere">关联条件</param>
        /// <returns>返回包括右表中的所有记录和左表中联结字段相等的记录</returns>
        ISelectSqlBuilder RightJoin<TClassA, TClassB>(Expression<Func<TClassA, TClassB, bool>> JoinWhere, string tableAliasB = "") where TClassA : class, new() where TClassB : class, new();

        /// <summary>
        /// 全联接
        /// </summary>
        /// <typeparam name="TClassB">B表</typeparam>
        /// <param name="JoinWhere">关联条件</param>
        /// <returns>只要其中某个表存在匹配，FULL JOIN 关键字就会返回行</returns>
        ISelectSqlBuilder FullJoin<TClassA, TClassB>(Expression<Func<TClassA, TClassB, bool>> JoinWhere, string tableAliasB = "") where TClassA : class, new() where TClassB : class, new();
        //-----------------------------------------------------------------------------------

        /// <summary>
        /// 通过条件查询表的记录数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        long Count<TClass>(Expression<Func<TClass, bool>> where) where TClass : class, new();
        //-----------------------------------------------------------------------------------
        ///// <summary>
        ///// 通过条件查询表的记录数
        ///// </summary>
        ///// <typeparam name="T">实体类型</typeparam>
        ///// <param name="condition">条件</param>
        ///// <returns></returns>
        //long GetCount();
        /// <summary>
        /// 判断实体是否存在表中
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        bool Exists<TClass>(Expression<Func<TClass, bool>> where) where TClass : class, new();

        /// <summary>
        /// 查寻返回实体集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        List<T> Query<T>() where T : class;
        /// <summary>
        /// 查寻返回单行实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        T GetSingleRow<T>() where T : class;

        /// <summary>
        /// 分页查询返回实体集合
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="pageIndex">第几页，从1开始</param>
        /// <param name="pageSize">每页多少</param>
        /// <param name="totalNumber">查询的记录数量</param>
        /// <returns></returns>
        List<TClass> QueryPagingList<TClass>(int pageIndex, int pageSize, ref long totalNumber) where TClass : class, new();
        /// <summary>
        /// 分页查询返回实体集合
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="pagingInfo">分页信息</param>
        /// <returns></returns>
        List<TClass> QueryPagingList<TClass>(BasePagingInfo pagingInfo) where TClass : class, new();
        //---------------------------------------------------------------------------------
#if !NET40
        /// <summary>
        /// 分页查询返回实体集合
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="pageIndex">第几页，从1开始</param>
        /// <param name="pageSize">每页多少</param>
        /// <param name="totalNumber">查询的记录数量</param>
        /// <returns></returns>
        Task<List<TClass>> QueryPagingListAsync<TClass>(int pageIndex, int pageSize, ref long totalNumber) where TClass : class, new();
        /// <summary>
        /// 分页查询返回实体集合
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="pagingInfo">分页信息</param>
        /// <returns></returns>
        Task<List<TClass>> QueryPagingListAsync<TClass>(BasePagingInfo pagingInfo) where TClass : class, new();

        /// <summary>
        /// 异步通过条件查询表的记录数
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        Task<long> CountAsync<TClass>(Expression<Func<TClass, bool>> where) where TClass : class, new();
        /// <summary>
        /// 异步返回实体集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        Task<List<T>> QueryAsync<T>() where T : class;
        /// <summary>
        /// 异步返回单行实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        Task<T> GetSingleRowAsync<T>() where T : class;
#endif
    }
    #endregion
    #region ISelectSqlBuilder<TClass>
    /// <summary>
    /// 单表操作
    /// </summary>
    /// <typeparam name="TClass"></typeparam>
    public partial interface ISelectSqlBuilder<TClass> : IQuerySql where TClass : class, new()
    {
        /// <summary>
        /// 最多返回记录数
        /// </summary>
        /// <param name="top"></param>
        /// <returns></returns>
        ISelectSqlBuilder<TClass> Top(int top = 1000);
        /// <summary>
        /// 添加查询字段
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="field"></param>
        /// <returns></returns>
        ISelectSqlBuilder<TClass> Fields(Expression<Func<TClass, dynamic>> fields = null);
        /// <summary>
        /// 添加查询字段
        /// </summary>
        /// <param name="fields"></param>
        /// <returns></returns>
        ISelectSqlBuilder<TClass> Fields(params string[] fields);

        /// <summary>
        /// 添加条件表达式
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="where"></param>
        /// <returns></returns>
        ISelectSqlBuilder<TClass> Where(Expression<Func<TClass, bool>> where, bool isAnd = true);
        /// <summary>
        /// 添加条件表达式
        /// </summary>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        ISelectSqlBuilder<TClass> Where(string whereStr, params CNDbParameter[] dbParameters);

        /// <summary>
        /// 按列排序，OrderBy<TClass>(a => a.Time)
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="column"></param>
        /// <returns></returns>
        ISelectSqlBuilder<TClass> OrderBy(Expression<Func<TClass, dynamic>> column);
        /// <summary>
        /// 按列倒向排序，OrderByDescending<TClass>(a => a.Time)
        /// </summary>
        /// <param name="column">列</param>
        /// <returns></returns>
        ISelectSqlBuilder<TClass> OrderByDescending(Expression<Func<TClass, dynamic>> column);

        /// <summary>
        /// 按列分组查询 GroupBy<TClass>(a => a.Time)
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="column"></param>
        /// <returns></returns>
        ISelectSqlBuilder<TClass> GroupBy(Expression<Func<TClass, dynamic>> column);
        /// <summary>
        /// HAVING 子句可以筛选分组后的各组数据
        /// </summary>
        /// <param name="expression">HAVING aggregate_function(column_name) operator value</param>
        /// <returns></returns>
        ISelectSqlBuilder<TClass> Having(Expression<Func<TClass, bool>> expression);

        //-----------------------------------------------------------------------------------

        /// <summary>
        /// 通过条件查询表的记录数
        /// </summary>
        /// <returns></returns>
        long Count(Expression<Func<TClass, bool>> where);
        //-----------------------------------------------------------------------------------

        /// <summary>
        /// 判断实体是否存在表中
        /// </summary>
        /// <returns></returns>
        bool Exists(Expression<Func<TClass, bool>> where);

        /// <summary>
        /// 查寻返回实体集合
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <returns></returns>
        List<TClass> Query();
        /// <summary>
        /// 查寻返回单行实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        TClass GetSingleRow();

        /// <summary>
        /// 分页查询返回实体集合
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="pageIndex">第几页，从1开始</param>
        /// <param name="pageSize">每页多少</param>
        /// <param name="totalNumber">查询的记录数量</param>
        /// <returns></returns>
        List<TClass> QueryPagingList(int pageIndex, int pageSize, ref long totalNumber);
        /// <summary>
        /// 分页查询返回实体集合
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="pagingInfo">分页信息</param>
        /// <returns></returns>
        List<TClass> QueryPagingList(BasePagingInfo pagingInfo);
        //---------------------------------------------------------------------------------
#if !NET40
        /// <summary>
        /// 分页查询返回实体集合
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="pageIndex">第几页，从1开始</param>
        /// <param name="pageSize">每页多少</param>
        /// <param name="totalNumber">查询的记录数量</param>
        /// <returns></returns>
        Task<List<TClass>> QueryPagingListAsync(int pageIndex, int pageSize, ref long totalNumber);
        /// <summary>
        /// 分页查询返回实体集合
        /// </summary>
        /// <typeparam name="TClass"></typeparam>
        /// <param name="pagingInfo">分页信息</param>
        /// <returns></returns>
        Task<List<TClass>> QueryPagingListAsync(BasePagingInfo pagingInfo);

        /// <summary>
        /// 异步通过条件查询表的记录数
        /// </summary>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        Task<long> CountAsync(Expression<Func<TClass, bool>> where);

        /// <summary>
        /// 异步返回实体集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        Task<List<TClass>> QueryAsync();
        /// <summary>
        /// 异步返回单行实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        Task<TClass> GetSingleRowAsync();
#endif
    }
    #endregion
}
